package nl.ViewerServer.Database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import nl.ViewerServer.ServerManager.UserManager.Objects.User;

import org.apache.log4j.Logger;

public class UserDatabaseConnection extends DatabaseConnection {
   protected static Logger     log = Logger.getLogger( UserDatabaseConnection.class );

   protected PreparedStatement getUser;
   protected PreparedStatement groupHasPrivilege;
   protected PreparedStatement getUserGroups;
   protected PreparedStatement getGroupParent;
   protected PreparedStatement getAllGroups;

   public UserDatabaseConnection() throws ClassNotFoundException, SQLException {
      super();
   }

   protected void prepareStatements() throws SQLException {
      super.prepareStatements();

      /*
       * User statements
       */
      getUser = conn.prepareStatement( "SELECT * FROM `user` WHERE `user`.`isDeleted` = false AND `user`.`username` = ?" );
      groupHasPrivilege = conn.prepareStatement( "SELECT COUNT(*) > 0 FROM `acl_privilege_group` where acl = ? and privilege = ? and groupId = ?" );
      getUserGroups = conn.prepareStatement( "SELECT `group`.`groupId` FROM `user` LEFT JOIN(`user_group`, `group`) ON (`group`.`isDeleted` = false AND `group`.`groupId` = `user_group`.`groupId` AND `user_group`.`userId` = `user`.`userId` ) WHERE `user`.`isDeleted` = false AND `user`.`userId` = ?" );
      getGroupParent = conn.prepareStatement( "SELECT * FROM `group` WHERE `isDeleted` = false AND `groupId` = ?" );
      getAllGroups = conn.prepareStatement( "SELECT * FROM `group` WHERE `isDeleted` = false" );

   }

   public synchronized User getUser( String username ) throws SQLException {
      getUser.clearParameters();
      getUser.setString( 1, username );
      ResultSet rs = getUser.executeQuery();
      if( !rs.next() )
         return null; // user not found

      // username column in the database has a unique constraint, so there should be either zero or just one result
      User u = new User( rs.getInt( "userId" ), username, rs.getString( "password" ), rs.getInt( "maxSessions" ),
            rs.getInt( "maxInstances" ), rs.getBoolean( "isDeleted" ), rs.getTimestamp( "altered" ) );

      rs.close();
      return u;
   }

   public synchronized boolean groupHasPrivilege( String acl, String privilege, int groupId ) throws SQLException {
      groupHasPrivilege.clearParameters();
      groupHasPrivilege.setString( 1, acl );
      groupHasPrivilege.setString( 2, privilege );
      groupHasPrivilege.setInt( 3, groupId );
      ResultSet rs = groupHasPrivilege.executeQuery();
      if( !rs.next() )
         return false; // this should not happen (the query should return either true of false)

      boolean res = rs.getBoolean( 1 );
      rs.close();

      return res;
   }

   public synchronized ArrayList<Integer> getUserGroupIds( User u ) throws SQLException {
      ArrayList<Integer> userGroupIds = new ArrayList<Integer>();
      getUserGroups.clearParameters();
      getUserGroups.setInt( 1, u.userId );
      ResultSet rs = getUserGroups.executeQuery();
      while( rs.next() ) {
         userGroupIds.add( rs.getInt( "groupId" ) );
      }
      rs.close();

      if( userGroupIds.size() == 0 )
         return userGroupIds;

      // recursive queries are not (efficiently) possible in mysql, just fetch all groups and do it ourselves
      rs = getAllGroups.executeQuery();
      HashMap<Integer, Integer> groupToParentGroup = new HashMap<Integer, Integer>();

      while( rs.next() ) {
         // check the rs.getObject (instead of rs.getInt too prevent loss of the NULL information in the database
         Integer parentGroupId = (Integer)rs.getObject( "parentGroupId" );
         groupToParentGroup.put( rs.getInt( "groupId" ), parentGroupId );
      }
      rs.close();

      // get the size here to only loop through the direct user groups in the outer for-loop
      int i = 0;
      while( i < userGroupIds.size() ) {
         Integer parentGroupId = groupToParentGroup.get( userGroupIds.get( i ) );
         if( parentGroupId != null && !userGroupIds.contains( parentGroupId ) )
            userGroupIds.add( parentGroupId );

         i++;
      }
      return userGroupIds;

   }
}
